package db.commodity;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import db.Database;
import dbservice.commodity.CommodityTypeDBService;
import po.AccountBookPo;
import po.CommodityTypePo;
import po.Po;

public class CommodityTypeDB implements CommodityTypeDBService{
	
	
	public static void main(String[] args){
		
	try {
		Database.iniDatabase("127.0.0.1",3306,"seac2?useUnicode=true&characterEncoding=utf-8","developer", "19960623");
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	AccountBookPo a=new AccountBookPo();
	a.setName("default");
	CommodityTypePo po=new CommodityTypePo();
	po.set名称("Giant");
	//po.set编号("X-00001");
	po.set父节点("bike");
	try {
		CommodityTypeDB db=new CommodityTypeDB(a);
	/*	db.update(po);
		List<CommodityTypePo> list=db.getFatherAbleType();
		for(int i=0;i<list.size();i++){
			System.out.println(list.get(i).get编号());
		}*/
		Po rs=db.add(po);
		//Po rs=db.delete(po);
		if(rs.isSuccessed()){
			System.out.println("添加成功！");
		}
		else{
			System.out.println(rs.getFailMessage());
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	}
	
	
	
	
	

	AccountBookPo accountBookPo;
	public CommodityTypeDB(AccountBookPo accountBookPo) throws SQLException {
	    String createT_CommodityType="CREATE TABLE IF NOT EXISTS `seac2`.`"+accountBookPo.getName()+ "_commodityType` (`f_commodityTypeID` varchar(128) NOT NULL,`f_name` varchar(20) NOT NULL, `f_father` varchar(20) NOT NULL, `f_isLeaf` int(1) NOT NULL DEFAULT '1',`f_hasCommodity` INT( 1 ) NOT NULL DEFAULT  '0',PRIMARY KEY (`f_commodityTypeID`),UNIQUE KEY `f_commodityTypeID` (`f_commodityTypeID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(createT_CommodityType);
		s.close();
		this.accountBookPo=accountBookPo;
	}
	
	
	public static String setCommodityTypeId(String prefix,int num) {
		//Random r = new Random();  
		//long code=r.nextInt(10000);
		num++;
	    NumberFormat nf = new DecimalFormat("00000");
	    String _num = nf.format(num);
        //String str = new SimpleDateFormat("yyyyMMdd").format(new Date());
        return prefix+"-"+_num;
    }

	/**
	 * 得到与参数同名称的商品分类
	 * @return
	 * 返回该商品分类的详细信息
	 * 
	 */
	public CommodityTypePo get(CommodityTypePo commoditytype){
		return null;
	}
	
	/**
	 * 得到所有商品分类
	 * 
	 * @return
	 * 返回商品分类列表中的所有商品分类
	 * 
	 */
	public List<CommodityTypePo> getAll(){
		List<CommodityTypePo> list=new ArrayList<CommodityTypePo>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();//ResultSet rs;
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodityType order by f_commodityTypeID");
			while(rs.next()){
			CommodityTypePo a=new CommodityTypePo();
			a.set编号(rs.getString("f_commodityTypeID"));
			a.set名称(rs.getString("f_name"));
			a.set父节点(rs.getString("f_father"));
			a.set是叶节点(rs.getInt("f_isLeaf")==1?true:false);
			list.add(a);
			}
			} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * 向商品分类列表添加商品分类
	 * @param commoditytype
	 * 需要添加的商品分类的详细信息
	 * @return
	 * 是否添加成功
	 */
	public Po add(CommodityTypePo commoditytype){
		String name=commoditytype.get名称();
		String father=commoditytype.get父节点();
		Po m=new Po(); String prefix="X";
		Connection c=Database.getConnection();
		Statement s;
		try {
			int total=0;
			s = c.createStatement();int hasCommodity = 0;ResultSet rs;
			ResultSet rs1=s.executeQuery("select max(f_commodityTypeID) from "+accountBookPo.getName()+"_commodityType where f_father='root'");
			while(rs1.next()){
				if(rs1.getString(1)!=null)
			          total=Integer.parseInt(rs1.getString(1).substring(prefix.length()+1));
			}
			if(father==null){
			s.execute("insert into "+accountBookPo.getName()+"_commodityType(f_commodityTypeID,f_name,f_father) values('"+setCommodityTypeId(prefix,total)+"','"+name+"','"+"root"+"')");
			m.successed();
			}
			else{
			rs=s.executeQuery("select f_hasCommodity from "+accountBookPo.getName()+"_commodityType where f_name='"+father+"'");
			if(rs.next()){
				hasCommodity=rs.getInt("f_hasCommodity");
			
			if(hasCommodity==1){
			  m.failed("您的父亲商品分类下已经存在商品，故您不可以再添加子分类！");
			}
			else if(hasCommodity==0){
				String fatherId = "";int childnum=0;
				ResultSet rs2=s.executeQuery("select f_commodityTypeID from "+accountBookPo.getName()+"_commodityType where f_name='"+father+"'");
				while(rs2.next()){
				 fatherId=rs2.getString("f_commodityTypeID");
				}
				ResultSet rs3=s.executeQuery("select max(f_commodityTypeID) from "+accountBookPo.getName()+"_commodityType where f_father='"+father+"'");
				while(rs3.next()){
					if(rs3.getString(1)!=null)
				          childnum=Integer.parseInt(rs3.getString(1).substring(fatherId.length()+1));
				}
				s.execute("insert into "+accountBookPo.getName()+"_commodityType(f_commodityTypeID,f_name,f_father) values('"+setCommodityTypeId(fatherId,childnum)+"','"+name+"','"+father+"')");
				s.executeUpdate("update "+accountBookPo.getName()+"_commodityType set f_isLeaf='0' where f_name='"+father+"'");
				m.successed();
			}
			}
			else{
				m.failed("您填写的父节点不存在！请重新填写！");
			}
			}
			
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	

	/**
	 * 删除含有相同关键字的商品分类
	 * @param keyword
	 */
	public Po delete(CommodityTypePo commoditytype){
		//String id=commoditytype.get编号();
		String name=commoditytype.get名称();String tmp=name;
		List<String> name2=new ArrayList<String>();
		 
		Po m=new Po(); 
		Connection c=Database.getConnection();
		Statement s;
		try {
			
			s = c.createStatement();
				if(!hasCommodity(commoditytype)){
				ResultSet rs=s.executeQuery("select f_name from "+accountBookPo.getName()+"_commodityType where f_father='"+name+"'");
				while(rs.next()){
					name2.add(rs.getString("f_name"));
				}
			
				for(int i=0;i<name2.size();i++){
				s.execute("delete from "+accountBookPo.getName()+"_commodityType where f_name='"+name2.get(i)+"'");
				CommodityTypePo a=new CommodityTypePo();
				a.set名称(name2.get(i));
				delete(a);
				}
				
			s.execute("delete from "+accountBookPo.getName()+"_commodityType where f_name='"+tmp+"'");
			m.successed();
				}
				else{
					m.failed("您要删除的分类下有商品，所以不能进行删除操作！");
				}
			s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
	
	/**
	 * 更新同名称的商品分类信息
	 * @param commoditytype
	 * 新的商品分类信息（编号不能更改）
	 * @return	
	 * 是否更新成功
	 */
	public Po update(CommodityTypePo commoditytype){
		Po po=new Po();
		String id=commoditytype.get编号();
		String name=commoditytype.get名称();
		//String father=commoditytype.get父节点();
		Connection c=Database.getConnection();
		Statement s;
		try {
		    s = c.createStatement();
			if(name!=null){
				s.addBatch("update "+accountBookPo.getName()+"_commodityType set f_name='"+name+"' where f_commodityTypeID='"+id+"'");
			}
			/*if(father!=null){
				s.addBatch("update "+accountBookPo.getName()+"_commodityType set f_father='"+father+"' where f_commodityTypeID='"+id+"'");
			}*/
			s.executeBatch();
			s.close();
			po.successed();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}		
		return po;
	}


	/**
	 * 查找含有相同关键字的商品分类
	 * @param keyword
	 */
	public Po find(CommodityTypePo commoditytype){
		return null;
	}
	@SuppressWarnings("unused")
	public  boolean hasCommodity(CommodityTypePo commoditytype){
		//String id=commoditytype.get编号();
		String name=commoditytype.get名称();
		List<String> name2=new ArrayList<String>();int hasCommodity=0;
		Connection c=Database.getConnection();
		Statement s;
		try {
		    s = c.createStatement();
			
				ResultSet rs=s.executeQuery("select f_hasCommodity from "+accountBookPo.getName()+"_commodityType where f_name='"+name+"'");
				if(rs.next()){
					hasCommodity=rs.getInt("f_hasCommodity");
					
				}
				ResultSet rs2=s.executeQuery("select f_name from "+accountBookPo.getName()+"_commodityType where f_father='"+name+"'");
				while(rs2.next()){
					name2.add(rs2.getString("f_name"));
				}
				for(int i=0;i<name2.size();i++){
					CommodityTypePo a=new CommodityTypePo();
					a.set名称(name2.get(i));
					return hasCommodity(a);
				}
			
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return hasCommodity==1? true:false;
	}


	@Override
	public List<CommodityTypePo> getLeaf() {
		List<CommodityTypePo> list=new ArrayList<CommodityTypePo>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodityType where f_isLeaf='1' order by f_commodityTypeID");
			while(rs.next()){
			CommodityTypePo a=new CommodityTypePo();
			a.set编号(rs.getString("f_commodityTypeID"));
			a.set名称(rs.getString("f_name"));
			a.set父节点(rs.getString("f_father"));
			a.set是叶节点(rs.getInt("f_isLeaf")==1?true:false);
			list.add(a);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


	@Override
	public List<CommodityTypePo> getFatherAbleType() {
		List<CommodityTypePo> list=new ArrayList<CommodityTypePo>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodityType where f_hasCommodity=0 order by f_commodityTypeID");
			while(rs.next()){
			CommodityTypePo a=new CommodityTypePo();
			a.set编号(rs.getString("f_commodityTypeID"));
			a.set名称(rs.getString("f_name"));
			a.set父节点(rs.getString("f_father"));
			a.set是叶节点(rs.getInt("f_isLeaf")==1?true:false);
			list.add(a);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
}
